CREATE TABLE users (
	id int not null primary key IDENTITY(1,1),
	fname nvarchar(16),
	lname nvarchar(32),
	email nvarchar(128), 
	phone nvarchar(16), 
	username nvarchar(32),
	password nvarchar(128),
	status bit,
	create_date datetime,
	last_update timestamp
);

CREATE TABLE user_roles (
	id int not null primary key IDENTITY(1,1),
	user_id int not null,
	role nvarchar(16),
	create_date datetime,
	last_update timestamp
);

CREATE TABLE teams (
	id int not null primary key IDENTITY(1,1),
	owner_id int not null,
	name nvarchar(64),
	status bit,
	create_date datetime, 
	last_update timestamp
);

CREATE TABLE team_requests (
	id int not null primary key IDENTITY(1,1),
	team_id int not null,
	request text,
	datetime_needed datetime, 
	city nvarchar(32),
	zipcode nvarchar(16),
	status bit, 
	create_date datetime,
	last_update timestamp
);

CREATE TABLE team_request_responses (
	id int not null primary key IDENTITY(1,1),
	team_request_id int not null, 
	agent_id int not null,
	team_request_position_id int not null, 
	note text, 
	available bit, 
	create_date datetime, 
	last_update timestamp
);

CREATE TABLE team_request_positions (
	id int not null primary key IDENTITY(1,1),
	team_request_id int not null,
	position nvarchar(16),
	status bit,
	create_date datetime,
	last_update timestamp
);

CREATE TABLE agent_requests (
	id int not null primary key IDENTITY(1,1),
	user_id int not null,
	request text,
	city nvarchar(32),
	zipcode nvarchar(16),
	handedness nvarchar(8),
	available_date datetime,
	status bit,
	create_date datetime,
	last_update timestamp
); 

CREATE TABLE agent_request_responses (
	id int not null primary key IDENTITY(1,1),
	agent_request_id int not null, 
	team_response_id int not null,
	note text, 
	available bit, 
	create_date datetime, 
	last_update timestamp
);

CREATE TABLE agent_request_positions (
	id int not null primary key IDENTITY(1,1),
	agent_request_id int not null,
	position nvarchar(32),
	status bit,
	create_date datetime,
	last_update timestamp
);